
<!DOCTYPE HTML>
<html lang="" >
    <head>
        <meta charset="UTF-8">
        <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
        <title>10.2 常用函数 · GitBook</title>
        <meta http-equiv="X-UA-Compatible" content="IE=edge" />
        <meta name="description" content="">
        <meta name="generator" content="GitBook 3.2.3">
        
        
        
    
    <link rel="stylesheet" href="../gitbook/style.css">

    
            
                
                <link rel="stylesheet" href="../gitbook/gitbook-plugin-highlight/website.css">
                
            
                
                <link rel="stylesheet" href="../gitbook/gitbook-plugin-search/search.css">
                
            
                
                <link rel="stylesheet" href="../gitbook/gitbook-plugin-fontsettings/website.css">
                
            
        

    

    
        
    
        
    
        
    
        
    
        
    
        
    

        
    
    
    <meta name="HandheldFriendly" content="true"/>
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <link rel="apple-touch-icon-precomposed" sizes="152x152" href="../gitbook/images/apple-touch-icon-precomposed-152.png">
    <link rel="shortcut icon" href="../gitbook/images/favicon.ico" type="image/x-icon">

    
    <link rel="next" href="design.html" />
    
    
    <link rel="prev" href="mysql.html" />
    

    </head>
    <body>
        
<div class="book">
    <div class="book-summary">
        
            
<div id="book-search-input" role="search">
    <input type="text" placeholder="Type to search" />
</div>

            
                <nav role="navigation">
                


<ul class="summary">
    
    

    

    
        
        
    
        <li class="chapter " data-level="1.1" data-path="../">
            
                <a href="../">
            
                    
                    理想未死
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.2" >
            
                <span>
            
                    
                    1. java基础
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.2.1" >
            
                <span>
            
                    
                    1.1 数据类型
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.2.1.1" data-path="../java/basic/string.html">
            
                <a href="../java/basic/string.html">
            
                    
                    1.1.1 字符串
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.2.1.2" data-path="../java/basic/num.html">
            
                <a href="../java/basic/num.html">
            
                    
                    1.1.2 数字
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.2.1.3" data-path="../java/basic/date.html">
            
                <a href="../java/basic/date.html">
            
                    
                    1.1.3 日期
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.2.2" >
            
                <span>
            
                    
                    1.2 集合
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.2.2.1" data-path="../java/basic/list.html">
            
                <a href="../java/basic/list.html">
            
                    
                    1.2.1 list
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.2.2.2" data-path="../java/basic/map.html">
            
                <a href="../java/basic/map.html">
            
                    
                    1.2.2 map
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.2.2.3" data-path="../java/basic/arr.html">
            
                <a href="../java/basic/arr.html">
            
                    
                    1.2.3 数组
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.2.3" data-path="../java/basic/guifan.html">
            
                <a href="../java/basic/guifan.html">
            
                    
                    1.3 编码规范
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.3" >
            
                <span>
            
                    
                    2. 版本控制
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.3.1" data-path="../java/git.html">
            
                <a href="../java/git.html">
            
                    
                    2.1 git
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.3.2" data-path="../java/gradle.html">
            
                <a href="../java/gradle.html">
            
                    
                    2.4 gradle
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.4" >
            
                <span>
            
                    
                    3. 常用技术
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.4.1" data-path="../java/patzn/poi.md">
            
                <span>
            
                    
                    3.1 poi
            
                </a>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.4.1.1" data-path="../java/poi.html">
            
                <a href="../java/poi.html">
            
                    
                    3.1.1 原生poi
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.4.1.2" data-path="../java/poi.html">
            
                <a href="../java/poi.html">
            
                    
                    3.1.2 easypoi
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.5" >
            
                <span>
            
                    
                    4. 常用框架
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.5.1" data-path="../java/example2.md">
            
                <span>
            
                    
                    4.1 springboot
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.5.2" data-path="../java/patzn/mybatis.md">
            
                <span>
            
                    
                    4.2 mybatisPlus
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.5.3" data-path="../java/ali.html">
            
                <a href="../java/ali.html">
            
                    
                    4.3 spring cloud alibaba
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.6" >
            
                <span>
            
                    
                    5. 中间件
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.6.1" data-path="../java/rocket.md">
            
                <span>
            
                    
                    5.1 rockermq
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.6.2" >
            
                <span>
            
                    
                    5.2 Arthas(线上诊断工具)
            
                </span>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.7" data-path="../java/jvm.html">
            
                <a href="../java/jvm.html">
            
                    
                    6. jvm
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.8" data-path="../java/example2.md">
            
                <span>
            
                    
                    7. 设计模式
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.9" data-path="../java/patzn/readme.md">
            
                <span>
            
                    
                    8. 工具类
            
                </a>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.9.1" data-path="../java/patzn/upload.md">
            
                <span>
            
                    
                    8.1 上传&下载
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.9.2" data-path="../java/patzn/input.md">
            
                <span>
            
                    
                    8.2 导入
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.9.3" data-path="../java/patzn/debris.md">
            
                <span>
            
                    
                    8.3 其他
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.10" >
            
                <span>
            
                    
                    9. 前端
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.10.1" data-path="../js/debris.html">
            
                <a href="../js/debris.html">
            
                    
                    9.1 碎片
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.10.2" data-path="../java/example2.md">
            
                <span>
            
                    
                    9.2 vue
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.10.3" data-path="../java/example2.md">
            
                <span>
            
                    
                    9.3 react
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.10.4" data-path="../java/example2.md">
            
                <span>
            
                    
                    9.4 node.js
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.11" >
            
                <span>
            
                    
                    10. 数据库
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.11.1" data-path="mysql.html">
            
                <a href="mysql.html">
            
                    
                    10.1 设计规范
            
                </a>
            

            
        </li>
    
        <li class="chapter active" data-level="1.11.2" data-path="pgsql.html">
            
                <a href="pgsql.html">
            
                    
                    10.2 常用函数
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.11.3" data-path="design.html">
            
                <a href="design.html">
            
                    
                    10.3 海量数据处理
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.12" >
            
                <span>
            
                    
                    11. 运维
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.12.1" data-path="../linux/linux.html">
            
                <a href="../linux/linux.html">
            
                    
                    11.1 linux
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.12.2" >
            
                <span>
            
                    
                    11.2 docker
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.12.2.1" data-path="../linux/docker_basic.html">
            
                <a href="../linux/docker_basic.html">
            
                    
                    11.2.1 基础
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.12.2.2" data-path="../linux/docker_pro.html">
            
                <a href="../linux/docker_pro.html">
            
                    
                    11.2.2 进阶
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.12.3" data-path="../linux/nginx.html">
            
                <a href="../linux/nginx.html">
            
                    
                    11.3 nginx
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.12.4" data-path="../linux/jenkins.html">
            
                <a href="../linux/jenkins.html">
            
                    
                    11.4 jenkins
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.13" data-path="../mac/experience.html">
            
                <a href="../mac/experience.html">
            
                    
                    12. 操作系统
            
                </a>
            

            
        </li>
    

    

    <li class="divider"></li>

    <li>
        <a href="https://www.gitbook.com" target="blank" class="gitbook-link">
            Published with GitBook
        </a>
    </li>
</ul>


                </nav>
            
        
    </div>

    <div class="book-body">
        
            <div class="body-inner">
                
                    

<div class="book-header" role="navigation">
    

    <!-- Title -->
    <h1>
        <i class="fa fa-circle-o-notch fa-spin"></i>
        <a href=".." >10.2 常用函数</a>
    </h1>
</div>




                    <div class="page-wrapper" tabindex="-1" role="main">
                        <div class="page-inner">
                            
<div id="book-search-results">
    <div class="search-noresults">
    
                                <section class="normal markdown-section">
                                
                                <h3 id="pgsql">pgsql</h3>
<blockquote>
<p><strong>&#x591A;&#x884C;&#x6570;&#x636E;&#x7528;&#x9017;&#x53F7;&#x62FC;&#x63A5;</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-keyword">SELECT</span>
  &#x5217;<span class="hljs-number">1</span>,string_agg(&#x5217;<span class="hljs-number">2</span>,<span class="hljs-string">&apos;,&apos;</span>) <span class="hljs-keyword">AS</span> &#x522B;&#x540D;
<span class="hljs-keyword">FROM</span>
  &#x8868;&#x540D;
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span>,&#x5217;<span class="hljs-number">1</span>;
</code></pre>
<blockquote>
<p><strong>&#x4E24;&#x4E2A;&#x65E5;&#x671F;&#x4E4B;&#x95F4;&#x7684;&#x5929;&#x6570;</strong></p>
</blockquote>
<pre><code class="lang-sql">extract(day from (implement_date-now()))
</code></pre>
<blockquote>
<p><strong>&#x8BA1;&#x7B97;&#x4E24;&#x4E2A;&#x65F6;&#x95F4;&#x4E4B;&#x95F4;&#x7684;&#x5C0F;&#x65F6;&#x6570;</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">extract</span>(<span class="hljs-keyword">day</span> <span class="hljs-keyword">from</span> (<span class="hljs-keyword">end</span>-<span class="hljs-keyword">start</span>)) * <span class="hljs-number">24</span> + <span class="hljs-keyword">extract</span>(<span class="hljs-keyword">hour</span> <span class="hljs-keyword">from</span> (<span class="hljs-keyword">end</span>-<span class="hljs-keyword">start</span>)) <span class="hljs-keyword">from</span> a
</code></pre>
<blockquote>
<p><strong>&#x67E5;&#x8BE2;&#x4E00;&#x6BB5;&#x65F6;&#x95F4;&#x5185;&#x7684;&#x6240;&#x6709;&#x65E5;&#x671F; (&#x7EDF;&#x8BA1;&#x7528;)</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-comment">-- 1.&#x5E8F;&#x5217;&#x67E5;&#x8BE2;</span>
<span class="hljs-keyword">select</span> <span class="hljs-built_in">date</span>(t) <span class="hljs-keyword">as</span> <span class="hljs-keyword">day</span>
<span class="hljs-keyword">from</span> 
generate_series(<span class="hljs-string">&apos;2020-06-09&apos;</span>::<span class="hljs-built_in">date</span>,<span class="hljs-string">&apos;2020-11-03&apos;</span>, <span class="hljs-string">&apos;1 days&apos;</span>) <span class="hljs-keyword">as</span> t;
<span class="hljs-comment">-- 2.&#x9012;&#x5F52;&#x67E5;&#x8BE2;:&#x67E5;&#x8BE2;&#x6548;&#x7387;&#x6BD4;&#x5E8F;&#x5217;&#x5FEB;&#x4E00;&#x70B9;</span>
with recursive t(n) as (
    <span class="hljs-keyword">select</span> <span class="hljs-built_in">date</span>(<span class="hljs-string">&apos;2020-06-09&apos;</span>)
    <span class="hljs-keyword">union</span> all 
    <span class="hljs-keyword">select</span> n+<span class="hljs-number">1</span> <span class="hljs-keyword">from</span> t <span class="hljs-keyword">where</span> n &lt; <span class="hljs-built_in">date</span>(<span class="hljs-string">&apos;2020-11-03&apos;</span>)
)<span class="hljs-keyword">select</span> n <span class="hljs-keyword">as</span> <span class="hljs-keyword">day</span> <span class="hljs-keyword">from</span> t;
</code></pre>
<blockquote>
<p><strong>&#x6570;&#x636E;&#x4E2D;&#x6392;&#x5E8F;&#x5B57;&#x6BB5;&#x65E2;&#x6709;&#x6570;&#x5B57;&#x4E5F;&#x6709;&#x975E;&#x6570;&#x5B57;&#xFF0C;&#x8981;&#x6C42;&#x6570;&#x5B57;&#x6392;&#x5728;&#x524D;&#x9762;&#x4E14;&#x6B63;&#x5E8F;&#x6392;&#xFF0C;&#x975E;&#x6570;&#x5B57;&#x6392;&#x5728;&#x540E;&#x9762;&#x6B63;&#x5E8F;&#x6392;
&#x76F4;&#x63A5;order by &#x662F;&#x6709;&#x95EE;&#x9898;&#x7684;&#xFF0C;&#x5E26;&#x5C0F;&#x6570;&#x70B9;&#x7684;&#x4F4D;&#x7F6E;&#x4E0D;&#x56FA;&#x5B9A;&#xFF0C;&#x8FD8;&#x662F;&#x8981;&#x5C06;&#x7B26;&#x5408;&#x6570;&#x5B57;&#x7C7B;&#x578B;&#x7684;&#x8F6C;&#x6362;&#x6210;&#x6570;&#x5B57;&#x8FDB;&#x884C;&#x6392;&#x5E8F;</strong></p>
</blockquote>
<pre><code class="lang-sql">&#x76F4;&#x63A5;&#x8F6C;&#x6362; &#x975E;&#x6570;&#x5B57; &#x4F1A;&#x62A5;&#x9519; &#xFF0C;&#x9700;&#x8981;&#x4E00;&#x4E2A;&#x5224;&#x65AD;&#x662F;&#x5426;&#x662F;&#x6570;&#x5B57;&#x7684;&#x51FD;&#x6570;
<span class="hljs-keyword">CREATE</span>
<span class="hljs-keyword">OR</span> <span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">FUNCTION</span> isnumeric (txtStr <span class="hljs-built_in">VARCHAR</span>) <span class="hljs-keyword">RETURNS</span> <span class="hljs-built_in">BOOLEAN</span> <span class="hljs-keyword">AS</span> $$
<span class="hljs-keyword">BEGIN</span>
    <span class="hljs-keyword">RETURN</span> txtStr ~ <span class="hljs-string">&apos;^([0-9]+[.]?[0-9]*|[.][0-9]+)$&apos;</span> ;
<span class="hljs-keyword">END</span> ; $$ LANGUAGE &apos;plpgsql&apos;;

where isnumeric(code) = true  order by  cast(code,numeric) asc 
union all
where  isnumeric(code) = false order by code asc  //&#x5206;&#x522B;&#x6392;&#x5E8F;
</code></pre>
<blockquote>
<p><strong>&#x4E00;&#x4E2A;&#x6761;&#x4EF6;&#xFF0C;&#x591A;&#x4E2A;&#x503C;&#x7684;&#x6392;&#x5E8F; &#x7528; order by case when</strong></p>
</blockquote>
<pre><code class="lang-sql">&#x6839;&#x636E;&#x4E00;&#x4E2A;&#x6761;&#x4EF6;&#x7684;&#x591A;&#x4E2A;&#x503C;&#xFF0C;&#x8FDB;&#x884C;&#x6392;&#x5E8F;&#x3002; 

     order by
     case when status=1 then 0
             when status=0 then 1
             when status=4 then 2
             when status=3 then 3
             when status=2 then 4 ,
    add_time desc

&#x6B64;&#x53E5;sql&#x7684;&#x610F;&#x601D;&#x662F;&#xFF1A; &#x5148;&#x6839;&#x636E;&#x7528;&#x6237;&#x72B6;&#x6001;&#x6392;&#x5E8F;&#xFF1A;   &#x72B6;&#x6001;&#x4E3A;1&#x7684;&#x6392;&#x5728;&#x6700;&#x524D;&#x9762;  &gt;&#x5176;&#x6B21;&#x662F;&#x72B6;&#x6001;&#x4E3A;0  &gt;&#x72B6;&#x6001;&#x4E3A;4  &gt;&#x72B6;&#x6001;&#x4E3A;3  &gt;&#x72B6;&#x6001;&#x4E3A;2 ; &#x518D;&#x6839;&#x636E;&#x6DFB;&#x52A0;&#x65F6;&#x95F4;&#x964D;&#x5E8F;&#x6392;&#x5E8F;&#x3002;
</code></pre>
<blockquote>
<p><strong>&#x5728;&#x805A;&#x5408;&#x51FD;&#x6570;&#x4E2D;&#x4F7F;&#x7528;case when  &#x5F97;&#x5230;&#x7B26;&#x5408;&#x8981;&#x6C42;&#x7684;&#x6570;&#x91CF;</strong></p>
</blockquote>
<pre><code class="lang-sql"> sum(case when s.status  &lt;![CDATA[&lt;]]&gt; 300 then 1 else 0 end ) as num
</code></pre>
<blockquote>
<p><strong>&#x8FD1; &#x4E00;&#x6BB5;&#x65F6;&#x95F4;</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-comment">--&#x8FD1;&#x4E00;&#x5468;</span>
  <span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>() - <span class="hljs-built_in">interval</span> <span class="hljs-string">&apos;1 week&apos;</span>;

<span class="hljs-comment">--&#x8FD1;&#x4E00;&#x6708;</span>
 <span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>() - <span class="hljs-built_in">interval</span> <span class="hljs-string">&apos;1 month&apos;</span>;

<span class="hljs-comment">--&#x8FD1;&#x4E00;&#x5E74;</span>
 <span class="hljs-keyword">select</span> <span class="hljs-keyword">now</span>() - <span class="hljs-built_in">interval</span> <span class="hljs-string">&apos;1 year&apos;</span>;
</code></pre>
<blockquote>
<p><strong>&#x5F53;&#x5929;&#xFF0C;&#x5468;&#xFF0C;&#x6708;</strong></p>
</blockquote>
<pre><code class="lang-sql">            &lt;choose&gt;
                &lt;when test=&quot;type == 0&quot;&gt;
                    AND DATE_TRUNC(&apos;day&apos;, ctime) = CURRENT_DATE
                &lt;/when&gt;
                &lt;when test=&quot;type == 1&quot;&gt;
                    AND ctime &gt;= (SELECT CURRENT_DATE -(EXTRACT (DOW FROM CURRENT_DATE) - 1 || &apos;day&apos;)::interval)
                    AND ctime &lt;![CDATA[&lt;]]&gt; (SELECT CURRENT_DATE -(EXTRACT (DOW FROM CURRENT_DATE) - 8
                    ||&apos;day&apos;)::INTERVAL)
                &lt;/when&gt;
                &lt;when test=&quot;type == 2&quot;&gt;
                    AND TO_CHAR(ctime,&apos;YYYY-MM&apos;) = TO_CHAR(CURRENT_DATE,&apos;YYYY-MM&apos;)
                &lt;/when&gt;
                &lt;otherwise&gt;
                    AND DATE_TRUNC(&apos;day&apos;, ctime) = CURRENT_DATE
                &lt;/otherwise&gt;
            &lt;/choose&gt;
</code></pre>
<blockquote>
<p><strong>&#x9664;&#x6CD5;&#x4FDD;&#x7559;&#x5C0F;&#x6570;</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">round</span>(<span class="hljs-number">1</span>::<span class="hljs-built_in">numeric</span>/<span class="hljs-number">4</span>::<span class="hljs-built_in">numeric</span>,<span class="hljs-number">2</span>);  //&#x4E0D;&#x8F6C;&#x6362;&#x7C7B;&#x578B;&#x7684;&#x8BDD;&#xFF0C;&#x7ED3;&#x679C;&#x4FDD;&#x7559;&#x6574;&#x6570;
</code></pre>
<blockquote>
<p><strong>&#x683C;&#x5F0F;&#x5316;&#x65E5;&#x671F; &#x5230; &#x5C0F;&#x65F6;</strong></p>
</blockquote>
<pre><code class="lang-sql">to_char(time, &apos;HH24:MI:SS&apos;)   //&#x6B63;&#x786E;
to_char(time, &apos;hh:mm:ss&apos;)    //&#x9519;&#x8BEF;

HH24    &#x4E00;&#x5929;&#x7684;&#x5C0F;&#x65F6;&#x6570;(00-23)
MI       &#x5206;&#x949F;(00-59)
</code></pre>
<blockquote>
<p><strong>&#x6570;&#x5B57;+&#x5B57;&#x6BCD;&#x6392;&#x5E8F;</strong></p>
</blockquote>
<pre><code class="lang-sql">sort &#x662F;&#x81EA;&#x5DF1;&#x2795;&#x7684;&#x4E00;&#x4E2A;&#x6392;&#x5E8F;&#x5B57;&#x6BB5;&#xFF0C;&#x6570;&#x5B57;&#x7C7B;&#x578B;
(split_part(sample_code, &apos;-&apos;, 1) ),sort  &#x7684;&#x610F;&#x601D;&#x662F;&#x5148;&#x6309;&#x5B57;&#x6BCD;&#x518D;&#x6309; &#x5B57;&#x6BCD;&#x91CC;&#x7684;&#x6570;&#x5B57;&#x6392;
</code></pre>
<blockquote>
<p><strong>&#x5206;&#x7EC4;&#x540E;&#x7ED9;&#x6BCF;&#x7EC4;&#x5185;&#x7684;&#x6570;&#x636E;&#x52A0;&#x4E2A;&#x7F16;&#x53F7;</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">id</span>,<span class="hljs-keyword">name</span>,contract_sample_id,unit,remark,formula,test_value, row_number() <span class="hljs-keyword">over</span>(<span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">name</span>,contract_sample_id) <span class="hljs-keyword">as</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">from</span> manufact_item
</code></pre>
<blockquote>
<p><strong>&#x4E0D;&#x7528;&#x521B;&#x5EFA;&#x51FD;&#x6570;&#xFF0C;&#x5FEB;&#x901F;&#x63D2;&#x5165;10&#x4E07;&#x6761;&#x6570;&#x636E;</strong></p>
</blockquote>
<pre><code class="lang-sql"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> tbl_test <span class="hljs-keyword">select</span> generate_series(<span class="hljs-number">1</span>,<span class="hljs-number">100000</span>),<span class="hljs-keyword">md5</span>(random()::<span class="hljs-built_in">text</span>),clock_timestamp();
</code></pre>
<blockquote>
<p><strong>&#x6839;&#x636E;&#x591A;&#x4E2A;&#x503C;&#x8FDB;&#x884C;&#x6A21;&#x7CCA;&#x641C;&#x7D22;</strong></p>
</blockquote>
<pre><code class="lang-sql"> &#x6162;&#x5168;&#x8868;&#x626B;&#x63CF; 
 <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> env_contract c <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">like</span> <span class="hljs-keyword">any</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-string">&apos;%&apos;</span>||regexp_split_to_table (<span class="hljs-string">&apos;1&#x3001;&#x9879;&#x76EE;&#x540D;1&apos;</span>, <span class="hljs-string">&apos;&#x3001;&apos;</span>)||<span class="hljs-string">&apos;%&apos;</span>)
 &#x5FEB;
 <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> manufact_contract  <span class="hljs-keyword">WHERE</span> contract_function SIMILAR <span class="hljs-keyword">to</span> <span class="hljs-string">&apos;%(&#x9879;&#x76EE;|3)%&apos;</span>
</code></pre>
<blockquote>
<p><strong>&#x884C;&#x8F6C;&#x591A;&#x5217;</strong></p>
</blockquote>
<pre><code class="lang-java">    &lt;select id=&quot;getJson&quot; resultType=&quot;java.lang.String&quot;&gt;
        with t1 as (select roster_date,week,
        &lt;foreach collection=&quot;rosters&quot; item=&quot;roster&quot; index=&quot;index&quot; separator=&quot;,&quot;&gt;
            string_agg((case key when #{roster} then attendance end), &apos;&apos;) as ${roster}
        &lt;/foreach&gt;
        from roster where year = #{year} and month = #{month} and company_id = #{companyId}
        group by roster_date,week
        order by roster_date ASC),
        t2 as (select row_to_json(t1) as json
        from t1)
        select &apos;[&apos; || string_agg(cast(json as text), &apos;,&apos;) || &apos;]&apos; as json from t2
    &lt;/select&gt;
          // &#x5176;&#x4E2D;rosters&#x662F;&#x5217;&#x540D;&#x79F0;&#xFF0C;&#x5F53;&#x4F5C;&#x53C2;&#x6570;&#x4F20;&#x8FDB;&#x6765;
</code></pre>

                                
                                </section>
                            
    </div>
    <div class="search-results">
        <div class="has-results">
            
            <h1 class="search-results-title"><span class='search-results-count'></span> results matching "<span class='search-query'></span>"</h1>
            <ul class="search-results-list"></ul>
            
        </div>
        <div class="no-results">
            
            <h1 class="search-results-title">No results matching "<span class='search-query'></span>"</h1>
            
        </div>
    </div>
</div>

                        </div>
                    </div>
                
            </div>

            
                
                <a href="mysql.html" class="navigation navigation-prev " aria-label="Previous page: 10.1 设计规范">
                    <i class="fa fa-angle-left"></i>
                </a>
                
                
                <a href="design.html" class="navigation navigation-next " aria-label="Next page: 10.3 海量数据处理">
                    <i class="fa fa-angle-right"></i>
                </a>
                
            
        
    </div>

    <script>
        var gitbook = gitbook || [];
        gitbook.push(function() {
            gitbook.page.hasChanged({"page":{"title":"10.2 常用函数","level":"1.11.2","depth":2,"next":{"title":"10.3 海量数据处理","level":"1.11.3","depth":2,"path":"sql/design.md","ref":"sql/design.md","articles":[]},"previous":{"title":"10.1 设计规范","level":"1.11.1","depth":2,"path":"sql/mysql.md","ref":"sql/mysql.md","articles":[]},"dir":"ltr"},"config":{"gitbook":"*","theme":"default","variables":{},"plugins":["livereload"],"pluginsConfig":{"livereload":{},"highlight":{},"search":{},"lunr":{"maxIndexSize":1000000,"ignoreSpecialCharacters":false},"sharing":{"facebook":true,"twitter":true,"google":false,"weibo":false,"instapaper":false,"vk":false,"all":["facebook","google","twitter","weibo","instapaper"]},"fontsettings":{"theme":"white","family":"sans","size":2},"theme-default":{"styles":{"website":"styles/website.css","pdf":"styles/pdf.css","epub":"styles/epub.css","mobi":"styles/mobi.css","ebook":"styles/ebook.css","print":"styles/print.css"},"showLevel":false}},"structure":{"langs":"LANGS.md","readme":"README.md","glossary":"GLOSSARY.md","summary":"SUMMARY.md"},"pdf":{"pageNumbers":true,"fontSize":12,"fontFamily":"Arial","paperSize":"a4","chapterMark":"pagebreak","pageBreaksBefore":"/","margin":{"right":62,"left":62,"top":56,"bottom":56}},"styles":{"website":"styles/website.css","pdf":"styles/pdf.css","epub":"styles/epub.css","mobi":"styles/mobi.css","ebook":"styles/ebook.css","print":"styles/print.css"}},"file":{"path":"sql/pgsql.md","mtime":"2021-03-09T03:04:32.777Z","type":"markdown"},"gitbook":{"version":"3.2.3","time":"2021-03-20T06:16:02.011Z"},"basePath":"..","book":{"language":""}});
        });
    </script>
</div>

        
    <script src="../gitbook/gitbook.js"></script>
    <script src="../gitbook/theme.js"></script>
    
        
        <script src="../gitbook/gitbook-plugin-livereload/plugin.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-search/search-engine.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-search/search.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-lunr/lunr.min.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-lunr/search-lunr.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-sharing/buttons.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-fontsettings/fontsettings.js"></script>
        
    

    </body>
</html>

